﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace LiteFw
{
    /// <summary>
    /// use using call this.
    /// </summary>
    public class DbCmd : IDisposable
    {
        #region Base Properties and Functions

        private DbCmd()
        {
        }

        private DbProviderFactory _factory;
        public DbConnection Connection { get; private set; }
        public DbCommand Command { get; private set; }
        private DbType _dbType;

        /// <summary>
        /// For SQL Server, No Transaction
        /// </summary>
        /// <param name="strConnect"></param>
        /// <param name="timeout"></param>
        /// <returns></returns>
        public static DbCmd Create(string strConnect, int timeout = 10)
        {
            DbCmd cmd = new DbCmd();
            cmd._dbType = DbType.SqlServer;
            cmd._factory = SqlClientFactory.Instance;
            cmd.Connection = cmd._factory.CreateConnection();
            cmd.Connection.ConnectionString = strConnect;
            cmd.Command = cmd.Connection.CreateCommand();
            cmd.Command.CommandTimeout = timeout;
            return cmd;
        }

        /// <summary>
        ///  For SQL Server, With Transaction, Need using TransactionScope
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="timeout"></param>
        /// <returns></returns>
        public static DbCmd Create(DbConnection connection, int timeout = 10)
        {
            DbCmd cmd = new DbCmd();
            cmd._dbType = DbType.SqlServer;
            cmd._factory = SqlClientFactory.Instance;
            cmd.Connection = connection;
            cmd.Command = cmd.Connection.CreateCommand();
            cmd.Command.CommandTimeout = timeout;
            return cmd;
        }

        public static DbCmd Create(DbType dbType, DbProviderFactory dbFacotry, DbConnection connection, int timeout = 10)
        {
            DbCmd cmd = new DbCmd();
            cmd._dbType = dbType;
            cmd._factory = dbFacotry;
            cmd.Connection = connection;
            cmd.Command = cmd.Connection.CreateCommand();
            cmd.Command.CommandTimeout = timeout;
            return cmd;
        }

        /// <summary>
        ///  close connection
        /// </summary>
        public void Dispose()
        {
            if (Connection != null && Connection.State != System.Data.ConnectionState.Closed)
            {
                Connection.Close();
                Connection = null;
            }
        }

        private void Open()
        {
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
        }

        private string CheckParmName(string parmName)
        {
            switch (this._dbType)
            {
                case DbType.SqlServer:
                    if (!parmName.StartsWith("@"))
                        return "@" + parmName;
                    break;
                default:
                    break;
            }
            return parmName;
        }

        private DbParameter CreateParm(string name, object value)
        {
            DbParameter parm = _factory.CreateParameter();
            parm.ParameterName = this.CheckParmName(name);
            parm.Value = value;
            return parm;
        }

        public void AddParm(string parmName, object value)
        {
            var parm = this.CreateParm(parmName, value);
            Command.Parameters.Add(parm);
        }

        #endregion

        #region Execute and query

        /// <summary>
        /// Execute sql or storeprocedure, can use AddParm to add parameters.  Auto clear parameters when completed.
        /// </summary>
        /// <param name="sql">can with @parameter , cooperate with AddParm function</param>
        /// <param name="cmdType">CommandType</param>
        /// <returns>changed row count</returns>
        public int Execute(string sql, CommandType cmdType = CommandType.Text)
        {
            this.Open();
            this.Command.CommandText = sql;
            this.Command.CommandType = cmdType;
            int updateRowCount = this.Command.ExecuteNonQuery();
            this.Command.Parameters.Clear();
            return updateRowCount;
        }

        public T GetScalar<T>(string sql, CommandType cmdType = CommandType.Text)
        {
            this.Open();
            this.Command.CommandText = sql;
            object value = this.Command.ExecuteScalar();
            this.Command.Parameters.Clear();
            return DataType.To<T>(value);
        }

        public DataSet GetDataSet(string sql, CommandType cmdType = CommandType.Text)
        {
            this.Open();
            this.Command.CommandText = sql;
            this.Command.CommandType = cmdType;
            var dataAdapter = _factory.CreateDataAdapter();
            dataAdapter.SelectCommand = this.Command;
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds); 
            this.Command.Parameters.Clear();
            return ds;
        }

        public DataTable GetDataTable(string sql, CommandType cmdType = CommandType.Text)
        {
            this.Open();
            this.Command.CommandText = sql;
            this.Command.CommandType = cmdType;
            var dataAdapter = _factory.CreateDataAdapter();
            dataAdapter.SelectCommand = this.Command;
            DataTable dt = new DataTable();
            dataAdapter.Fill(dt);
            this.Command.Parameters.Clear();
            return dt;
        }

        #endregion


    }

    public enum DbType
    {
        SqlServer = 0
    }
}
